package com.renting.modules.renting.rentSys.dao;

import java.util.List;
import java.util.Optional;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import com.renting.modules.renting.rentSys.entity.House;

public interface HouseDao extends CrudRepository<House, String> {

	public Optional<House> findById(String id);

	/**
	 * 
	 * getNearbyHouses
	 * 
	 * @description:根据得到目标点一定范围内的房源
	 * @return
	 * @exception:执行的原生sql由于数据限制 这里
	 *                               distance > 50 ，需要改成distance < 5000
	 * @author administrator
	 * @param location
	 * @create date： 2018年3月16日 @remark：
	 */
	@Query(value = "select h.id,h.name,h.price,h.address,h.status,h.location,h.lat,h.lgt,h.update_time,h.remark,"
			+ "h.lived,h.total,h.create_time,h.contact,h.price,h.acreage,"
			+ "st_distance(point(:lgt,:lat),point(h.lgt,h.lat)) *111195  as distance from rent_house h "
			+ "having distance > 50 order by h.create_time desc ", nativeQuery = true)
	public List<House> getNearbyHouses(@Param(value = "lgt") String lgt, @Param(value = "lat") String lat);

}
